package com.cn.jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import javax.persistence.Column;
import javax.persistence.Table;
import javax.persistence.Transient;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

//import org.monday.springjdbc.dao.BaseDao;
//import org.monday.springjdbc.util.QueryResult;

public abstract class BaseImpl<T> implements Base<T> {

	private Map<String, String> CACHE_SQL = new HashMap<String, String>();
	public static final String SQL_INSERT = "insert";
	public static final String SQL_UPDATE = "update";
	public static final String SQL_DELETE = "delete";
	public static final String SQL_COLUMN = "column";
	public static final String SQL_DELETE_BATCH = "delete_batch";



	@Override
	public abstract NamedParameterJdbcTemplate getNamedJdbc();

//	private JdbcTemplate jdbcTemplate;

//	@Autowired
//	private DataSourceTransactionManager transactionManager;

	private Class<T> entityClass;

	@Override
	public abstract  JdbcTemplate getJdbcTemplate();


	@SuppressWarnings("unchecked")
	public BaseImpl() {
		ParameterizedType type = (ParameterizedType) getClass().getGenericSuperclass();
		entityClass = (Class<T>) type.getActualTypeArguments()[0];
		// INSERT_SQL=this.makeSql(SQL_INSERT);
		CACHE_SQL.put(SQL_INSERT, this.makeSql(SQL_INSERT));
		CACHE_SQL.put(SQL_UPDATE, this.makeSql(SQL_UPDATE));
		CACHE_SQL.put(SQL_DELETE, this.makeSql(SQL_DELETE));
		CACHE_SQL.put(SQL_COLUMN, getSqlPa(entityClass));
		CACHE_SQL.put(SQL_DELETE_BATCH, this.makeSql(SQL_DELETE_BATCH));
		
//		System.out.println(CACHE_SQL.get(SQL_COLUMN));
	}

	@Override
	public String columnStr(){
		return CACHE_SQL.get(SQL_COLUMN);
	}

	@Override
	public int count(String sql){
		try{
			int i = getJdbcTemplate().queryForObject(sql,Integer.class);
			return i;
		}catch (Exception e){
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public <T> Long save(T entity, String sql) {
		try {
			GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
			SqlParameterSource paramSource = new BeanPropertySqlParameterSource(entity);
			getNamedJdbc().update(sql, paramSource, generatedKeyHolder);
			return generatedKeyHolder.getKey().longValue();
		} catch (Exception e) {
			e.printStackTrace();
			return 0L;
		}
	}
	@Override
	public Long save(T entity) {
		try {
			GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
			SqlParameterSource paramSource = new BeanPropertySqlParameterSource(entity);
			getNamedJdbc().update(this.makeSql(SQL_INSERT), paramSource, generatedKeyHolder);
			return generatedKeyHolder.getKey().longValue();
		} catch (Exception e) {
			e.printStackTrace();
			return 0L;
		}
	}

	@Override
	public  <T> void saveBatch(String sql,List<T> list) throws Exception {
		SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(list.toArray());
		getNamedJdbc().batchUpdate(sql, params);
	}

	@Override
	public void saveBatch(List<T> list) throws Exception {
		String sql2 = this.makeSql(SQL_INSERT);
		if (list == null || list.size() == 0) {
			list = new ArrayList<T>();
		}
		switch (0) {
		case 0:
			SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(list.toArray());
			getNamedJdbc().batchUpdate(sql2, params);
			break;
		case 1:
//		    insertSQLiteBatch(list, sql2);
			break;
		case 2:
			
			break;
		default:
			break;
		}
		
	}

	/*private void insertSQLiteBatch(List<T> list, String sql2) {
		DefaultTransactionDefinition dte= new DefaultTransactionDefinition();
		//设置隔离级别
		dte.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW); 
		TransactionStatus status = transactionManager.getTransaction(dte); 
		try {
			int index=list.size();
			for(int i=0;i<index;i++){
				GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
				SqlParameterSource paramSource = new BeanPropertySqlParameterSource(list.get(i));
				getNamedJdbc().update(sql2, paramSource, generatedKeyHolder);
			}
		    transactionManager.commit(status);
		} catch (Exception e) {
		    //出现异常回滚事务，以免出现脏数据，数据不完整的问题
		    transactionManager.rollback(status);
		}
	}*/

	@Override
	public void update(T entity) throws Exception {
		// GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
		SqlParameterSource paramSource = new BeanPropertySqlParameterSource(entity);
		getNamedJdbc().update(this.makeSql(SQL_UPDATE), paramSource);
	}

	@Override
	public <T> void update(String sql,T entity) throws Exception {
		// GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
		SqlParameterSource paramSource = new BeanPropertySqlParameterSource(entity);
		getNamedJdbc().update(sql, paramSource);
	}

	@Override
	public void updateBatch(List<T> list) throws Exception {
		String sql = this.makeSql(SQL_UPDATE);
		switch (0) {
		case 0:
			if (list == null || list.size() == 0) {
				list = new ArrayList<T>();
			}
			SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(list.toArray());
			getNamedJdbc().batchUpdate(sql, params);
			break;
		case 1:
			/*DefaultTransactionDefinition dte= new DefaultTransactionDefinition();
			//设置隔离级别
			dte.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
			TransactionStatus status = transactionManager.getTransaction(dte);
			try {
				int index=list.size();
				for(int i=0;i<index;i++){
					update(list.get(i));
				}
			    transactionManager.commit(status);
			} catch (Exception e) {
			    //出现异常回滚事务，以免出现脏数据，数据不完整的问题
			    transactionManager.rollback(status);
			}*/
			break;
		case 2:
			
			break;
		default:
			break;
		}
		
	}

	/*public void delete(T entity) throws Exception {
		// getNamedJdbc().u
	}*/
	@Override
	public void delete(Serializable id) throws Exception {
		getJdbcTemplate().update(CACHE_SQL.get(SQL_DELETE), id);
	}

	@Override
	@Transactional(rollbackFor={Exception.class})
	public void deleteBatch(List<Integer> list) throws Exception {
		if( !(list==null||list.size()==0) ){
			StringBuffer sql_pa=new StringBuffer();
			for(Serializable id : list){
				sql_pa.append(id).append(",");
			}
			sql_pa=sql_pa.deleteCharAt(sql_pa.length()-1);
			String sql=CACHE_SQL.get(SQL_DELETE_BATCH);
			sql=String.format(sql, sql_pa.toString());
			getJdbcTemplate().update(sql);
		}
	}
	@Override
	public List<T> getList(String sql, Map<String, Object> param) throws Exception {
		if (param == null) {
			param = new HashMap<String, Object>();
		}
		RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass);
		return getNamedJdbc().query(sql, param, rowMapper);
	}

	@Override
	public <T> List<T> getList(String sql, Class<T> mappedClass, Map<String, Object> param) throws Exception {
		RowMapper<T> rowMapper = (RowMapper<T>) BeanPropertyRowMapper.newInstance(mappedClass);
		return getNamedJdbc().query(sql.toString(), param, rowMapper);
	}
	@Override
	public <T> List<T> getList(String sql, Class<T> mappedClass, Object... param) throws Exception {
		Object[] args = null;
		int flag = 0;
		if (param == null) {
			args = new Object[0];
		} else {
			args = new Object[param.length];
			flag = param.length;
		}
		for (int i = 0; i < flag; i++) {
			args[i] = param[i];
		}
		RowMapper<T> rowMapper = (RowMapper<T>) BeanPropertyRowMapper.newInstance(mappedClass);
		return getJdbcTemplate().query(sql.toString(), args, rowMapper);
	}
	@Override
	public List<T> getListAll() throws Exception {
		Table table = entityClass.getAnnotation(Table.class);
		String tableName = null;
		if (table == null) {
			tableName = entityClass.getSimpleName();
		} else {
			tableName = table.name();
		}
		String sql = "SELECT "+CACHE_SQL.get(SQL_COLUMN)+" FROM " + tableName + " WHERE id=?";
		RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass);
		return getNamedJdbc().query(sql, rowMapper);
	}
	@Override
	public T get(Serializable id) throws Exception {
		Table table = entityClass.getAnnotation(Table.class);
		String tableName = null;
		if (table == null) {
			tableName = entityClass.getSimpleName();
		} else {
			tableName = table.name();
		}
		String sql = "SELECT "+CACHE_SQL.get(SQL_COLUMN)+" FROM " + tableName + " WHERE id=?";
		RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass);
		Object[] objs = { id };
		return getJdbcTemplate().queryForObject(sql, objs, rowMapper);
	}

	@Override
	public <T> T get(String sql ,Class<T> mappedClass, Object... param) throws Exception {

		RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(mappedClass);
		Object[] args = null;
		int flag = 0;
		if (param == null) {
			args = new Object[0];
		} else {
			args = new Object[param.length];
			flag = param.length;
		}
		for (int i = 0; i < flag; i++) {
			args[i] = param[i];
		}
		try{
			return getJdbcTemplate().queryForObject(sql, args, rowMapper);
		}catch (EmptyResultDataAccessException er){
			return null;
		}catch (Exception e){
			e.printStackTrace();
			return null;
		}

	}

	private StringBuffer checkCacheSql(String sqlFlag) {
		StringBuffer sql = new StringBuffer("");
		String val = null;
		if (sqlFlag.equals(SQL_INSERT)) {
			val = CACHE_SQL.get(SQL_INSERT);
		} else if (sqlFlag.equals(SQL_UPDATE)) {
			val = CACHE_SQL.get(SQL_UPDATE);
		} else if (sqlFlag.equals(SQL_DELETE)) {
			val = CACHE_SQL.get(SQL_DELETE);
		}
		if (val != null) {
			sql.append(val);
		}
		return sql;
	}

	public String getSqlPa(Class<T> c) {
		// Class c=(Class) obj;
		Field[] f = c.getDeclaredFields();
		StringBuffer sqlColumn = new StringBuffer();
		if (f.length != 0) {
			for (int i = 0; i < f.length; i++) {
				if (f[i].getName() == "id") {
					sqlColumn.append("id").append(",");
				} else {
					Transient tran = f[i].getAnnotation(Transient.class);
					Column column = f[i].getAnnotation(Column.class);
					if (tran != null) {
						continue;
					}
					if (isNotempty(column)) {
						sqlColumn.append(column.name() + " " + f[i].getName()).append(",");
					} else {
						sqlColumn.append(f[i].getName() + " " + f[i].getName()).append(",");
					}
				}
			}
			sqlColumn.deleteCharAt(sqlColumn.length() - 1);
		}
		return sqlColumn.toString();
	}

	// 组装SQL
	private String makeSql(String sqlFlag) {
		StringBuffer sql = checkCacheSql(sqlFlag);
		if (sql.length() != 0) {
			return sql.toString();
		}

		Field[] fields = entityClass.getDeclaredFields();
		Table table = entityClass.getAnnotation(Table.class);
		String tableName = null;
		if (table == null) {
			tableName = entityClass.getSimpleName();
		} else {
			tableName = table.name();
		}
		if (sqlFlag.equals(SQL_INSERT)) {
			sql.append(" INSERT INTO ").append(tableName);
			sql.append("(");
			for (int i = 0; fields != null && i < fields.length; i++) {
				fields[i].setAccessible(true); // 暴力反射
				// String column = fields[i].getName();
				// sql.append(column).append(",");
				Field oField = fields[i];
				Column oMyFieldAnnotation = oField.getAnnotation(Column.class);
				Transient tran = oField.getAnnotation(Transient.class);
				if (oField.getName().equals("id") || tran != null) { // id 代表主键
					continue;
				}
				// if(!"id".equalsIgnoreCase(oField.getName())){
				if (oMyFieldAnnotation != null) {
					sql.append(oMyFieldAnnotation.name()).append(",");
				} else {
					// String column = fields[i].getName();
					sql.append(oField.getName()).append(",");
				}
				// }

			}
			sql = sql.deleteCharAt(sql.length() - 1);
			sql.append(") VALUES (");
			for (int i = 0; fields != null && i < fields.length; i++) {
				Field oField = fields[i];
				Transient tran = oField.getAnnotation(Transient.class);
				if (oField.getName().equals("id") || tran != null) { // id 代表主键
					continue;
				}

				// if(!"id".equalsIgnoreCase(oField.getName())){
				sql.append(":").append(oField.getName()).append(",");
				// }
			}
			sql = sql.deleteCharAt(sql.length() - 1);
			sql.append(")");
		} else if (sqlFlag.equals(SQL_UPDATE)) {

			sql.append(" UPDATE ").append(tableName).append(" SET ");

			for (int i = 0; fields != null && i < fields.length; i++) {
				fields[i].setAccessible(true); // 暴力反射
				// Field oField = fields[i];
				Column oMyFieldAnnotation = fields[i].getAnnotation(Column.class);
				Transient tran = fields[i].getAnnotation(Transient.class);
				String column = fields[i].getName();
				if (column.equals("id") || tran != null) { // id 代表主键
					continue;
				}
				if (oMyFieldAnnotation != null) {
					// sql.append(oMyFieldAnnotation.name()).append(",");
					sql.append(oMyFieldAnnotation.name()).append("=:").append(column).append(",");
				} else {
					// String column = fields[i].getName();
					// sql.append(oField.getName()).append(",");
					sql.append(column).append("=:").append(column).append(",");
				}
			}
			sql = sql.deleteCharAt(sql.length() - 1);
			// sql.append(" WHERE id=?");
			sql.append(" WHERE id=:id");
		} else if (sqlFlag.equals(SQL_DELETE)) {
			// sql.append(" DELETE FROM " + entityClass.getSimpleName() + "
			// WHERE id=?");

			sql.append(" DELETE FROM ").append(tableName).append(" WHERE id=?");
		} else if (sqlFlag.equals(SQL_DELETE_BATCH)) {
			// sql.append(" DELETE FROM " + entityClass.getSimpleName() + "
			// WHERE id=?");

			sql.append(" DELETE FROM ").append(tableName).append(" WHERE id in (%s)");
		}
		// System.out.println("SQL=" + sql);
		return sql.toString();

	}

	/**
	 * 验证一个字符串或者数组不为空
	 *
	 * @param obj
	 * @return
	 */
	public static boolean isNotempty(Object obj) {
		if (obj == null) {
			return false;
		}
		if (obj instanceof List) {
			List list = (List) obj;
			return list.size() > 0;
		} else {
			return obj.toString().trim().length() > 0;
		}
	}
	
}
